from re import escape
from flask import Blueprint,request,jsonify
from utils.db import db
from utils.comm import get_data,xtree
from utils.comm import access_key,secret_key
#七牛云
from qiniu import Auth

import json
import os
bp_course = Blueprint('course',__name__)


#无限级分类
@bp_course.route('/cates/',methods=["POST","GET","DELETE"])
def cates():
    if request.method =="POST":
        data = get_data()
        sql = "insert into cates values(0,'%s',%d,%s) "%(data['name'],int(data['pid']),data['params'])
        
        db.insert(sql)
        return jsonify({"code":200})

    elif request.method =="GET":
        #获取全部数据
        sql = "select * from cates"
        res = db.find_all(sql)
        print(res)
        
        list = xtree(res)
        return jsonify({'code':200,'list':list})
    else:
        pass

#分页
@bp_course.route('/pageshow/',methods=["POST","GET","DELETE"])
def pageshow():
    if request.method =="POST":
        pass
    elif request.method =="GET":
        pagesize = int(request.args.get('page_size'))
        page = request.args.get('page')
        start = (int(page)-1)*pagesize

        sql = "select * from cates  order by id asc limit %d,%d"%(start,pagesize)
        res = db.find_all(sql)
        sql1 = "select count(id) as total from cates"
        res1 = db.find_one(sql1)
        print(res)
        return jsonify({"code":200,'list':res,'total':res1['total']})
    else:
        pass


#讲师展示添加
@bp_course.route('/teaadd/',methods=["POST","GET","DELETE"])
def teaadd():
    if request.method =="POST":
        data = get_data()
        if data['params']:
            sql = "insert into courses values(%d,'%s',%d,'%s','%s','%s',%d,%d)"%(0,data['title'],int(data['pid']),data['params'],data['pic'],data['descip'],int(data['teacher_id']),int(data['status']))
        else:
            sql = "insert into courses(`title`,`pid`,`pic`,`descip`,`teacher_id`,`status`) values('%s',%d,'%s','%s',%d,%d)"%(data['title'],int(data['pid']),data['pic'],data['descip'],int(data['teacher_id']),int(data['status']))
        db.insert(sql)
        return jsonify({"code":200})
    elif request.method =="GET":
        id =request.args.get('id')
        sql = "select * from cates where id=%d"%(int(id))
        res= db.find_one(sql)
        return jsonify({'code':200,'params':res['params']})
    else:
        pass

#coures 数据库的分页展示    章节添加
@bp_course.route('/courserpage/',methods=["POST","GET"])
def courserpage():
    if request.method =="POST":
        data = get_data()
        sql = "insert into jie values(0,'%s',%d,'%s',%d)"%(data['name'],int(data['course_id']),data['video_url'],int(data['sort']))
        print(sql)
        db.insert(sql)
        return jsonify({"code":200})
    elif request.method =="GET":
        #获取每页数据条数
        pagesize = int(request.args.get('page_size'))
        #获取数据当前页数
        page = request.args.get('page')
        #每页数据第一个数据下表
        start = (int(page)-1)*pagesize
        #构建sql查询
        sql  = "select l.title,l.pic,c.name from courses as l left join cates as c on l.pid=c.id limit %d,%d"%(start,pagesize)     
        res = db.find_all(sql)
        #查询数据总条数 用于前台显示
        sql1 = "select count(id) as total from courses"
        res1 = db.find_one(sql1)

        return jsonify({"code":200,'list':res,'total':res1['total']})

#获取七牛云的token
@bp_course.route('/qntoken/')
def qntoken():
    q = Auth(access_key,secret_key)
    #要上传的空间
    bucket_name = 'yangceshi'
    #生成上传token
    token = q.upload_token(bucket_name)
    return jsonify({"code":200,'token':token})

#章节表添加数据 展示
@bp_course.route('/jieadd/',methods=['POST','GET'])
def jieadd():
    if request.method =="POST":
        #接收视频流
        file = request.files['file']
        #接收课程id
        course_id = request.form['course_id']
        #写文件吧视频放在本地
        path = "./static/upload/"+str(course_id)
        #判断有没有地址 没有创建地址 有进行添加数据
        if not os.path.exists(path):
            os.mkdir(path)
        #保存视频
        file.save(os.path.join(path,file.filename))
        return jsonify({"code":200,'url':"/static/upload/"+str(course_id)+'/'+file.filename})


    elif request.method =="GET":
        #获取课程
        sql = "select * from courses "
        res = db.find_all(sql)
        return jsonify({"code":200,'list':res})



#课程展示
@bp_course.route('/course_datail/')
def course_datail():
    userid = 0
    #获取课程id
    id = request.args.get('id')
    #根据课程id查课程
    sql = "select c.id,c.title,c.params,c.descip,t.name,t.descrip from courses as c inner join teacher as t on c.teacher_id=t.id where c.id=%d "%int(id)
    course = db.find_one(sql)
    #根据id查询对应章节
    sql1="select * from jie where id=%d"%int(id)
    jie = db.find_all(sql1)
    #组装数据返回
    course['jielist'] = jie
    #查看用户收藏该商品了没
    #添加个字段返回去 默认都没有收藏
    course['follow'] = False
    try:
        # 获取用户id  根据用户id和课程id查询是否收藏 收藏状态改成true
        userid = int(request.args.get('userid'))
        sql2 =  "select * from user_follow_course where courseid=%d and userid=%d"%(int(id),userid)
        res2 = db.find_one(sql2)
        if res2:
            course['follow'] = True
    except:
        pass

    #最近浏览 需要和redis配合
    #先构造浏览数据 需要展示  只有优用户访问菜可以有浏览记录 需要用户id
    if userid>0:
        dict = {'id':course['id'],'title':course['title']}
        #先看redis有没有浏览记录 没有加 有就继续加 只能有4条超过就删除第一条
        #构造用户浏览记录用userid拼接一个字符串最好
        key = 'view'+str(userid)
        reslist = r.str_get(key)
        if reslist:
            #有的话看浏览记录是否大于等于4条 
            reslist = json.loads(reslist)
            #要判断用户重复浏览  不能把重复浏览的数据给用户看
            ids = [i['id'] for i in reslist]
            #如果浏览的id没在浏览记录里加入
            if course['id'] not in ids:
                if len(reslist)>4:
                    #删除第一个吧最后一个加入
                    reslist.pop(0)
                reslist.append(dict)
                #redis保留浏览记录
                r.str_set(key,json.dumps(reslist))
                
        #没有 就加入redis
        else:
            list = []
            list.append(dict)
            #redis保存浏览记录
            r.str_set(key,json.dumps(reslist))

    return jsonify({'code':200,'list':course})
    

from utils.redisconn import r
#点击收藏
@bp_course.route('/user_follow/')
def user_follow():
    #多对多  收藏需要用户id  收藏的课程
    id = int(request.args.get('id'))
    userid = int(request.args.get('userid'))

    #用户关注还是取消操作
    type = int(request.args.get('type'))
    if type == 1:
        #用户是否收藏过 查看收藏表
        sql = "select * from user_follow_course where courseid=%d and userid=%d"%(id,userid)
        res = db.find_one(sql)
        if res:
            return jsonify({'code':10030,'mas':'您已经收藏了该商品'})
        #没有藏就写入
        sql1 = "insert into  user_follow_course values(%d,%d)"%(userid,id)
        db.insert(sql1)
        mas = '收藏成功'
        
    else:
        sql = "delete from user_follow_course where userid=%d and courseid=%d " %(userid,id)
        db.delete(sql)
        mas = '取消收藏'

    #评论增加
    sql = "select id,follow_count from courses where id=%d "%(id)
    user = db.find_one(sql)
    count = int(user['follow_count'])
    if type == 1:
        count +=1
    else:
        if count>=1:
            count -=1
    sql2 = "update courses set follow_count=%d where id=%d"%(count,id)
    db.update(sql2)


    return jsonify({'code':200,'mas':mas}) 

#我的关注列表
@bp_course.route('/myfollow/')
def myfollow():
    #获取用户id
    userid = int(request.args.get('userid'))
    #在桥表和课程表链表查询 我关注了那些课程
    sql = "select c.id,c.title from  courses as c inner join user_follow_course as u on c.id=u.courseid where u.userid=%d "%(userid)
    res = db.find_all(sql)
    #返回数据展示
    return jsonify({"code":200,'list':res})

#我的浏览记录表
@bp_course.route('/viewlist/')
def viewlist():
    #获取用户浏览记录并返回
    userid= request.args.get('userid')
    key = 'view'+str(userid)
    res = r.str_get(key)
    #redis取出的值是b二进制 转换格式
    if res:
        res = json.loads(res)
    return jsonify({'code':200,'list':res})






